package DBLayer;

import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBPerson implements IFDBPerson {
	private Connection con;

	public DBPerson() {
		// tries to connect to the database
		con = DBConnection.getInstance().getDBcon();
	}

	// get all customer
	public ArrayList<Customer> listAllCustomers(boolean retriveAssociation) {
		return customerMiscWhere("", retriveAssociation);
	}

	// get all Employee
	public ArrayList<Employee> listAllEmployees(boolean retriveAssociation) {
		return employeeMiscWhere("", retriveAssociation);
	}

	// get one Customer having the phonenumber
	public Customer findCustomer(int phoneNumber, boolean retriveAssociation) {
		String wClause = "phoneNumber = '" + phoneNumber + "'";
		return singleWhereCustomer(wClause, retriveAssociation);
	}

	// get one Employee have the employeeId
	public Employee findEmployee(int phoneNumber, boolean retriveAssociation) {
		String wClause = "phoneNumber = '" + phoneNumber + "'";
		return singleWhereEmployee(wClause, retriveAssociation);
	}

	// insert a new customer
	public int insertCustomer(Customer cus) throws Exception {

		int rc = -1;
		String query = "INSERT INTO Person( name, adress, zipCode, phoneNumber, email, customerType, companyName, cvrNr) VALUES('"
				+

				cus.getName()
				+ "','"
				+ cus.getAdress()
				+ "','"
				+ cus.getZipCode()
				+ "','"
				+ cus.getPhoneNumber()
				+ "','"
				+ cus.getEmail()
				+ "','"
				+ cus.getCustomerType()
				+ "','"
				+ cus.getCompanyName() + "','" + cus.getCvrNr() + "'" + ")";

		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (SQLException ex) {
			System.out.println("customer ikke oprettet");
			throw new Exception("Customer is not inserted correct");
		}
		return (rc);
	}

	// inserts an employee
	public int insertEmployee(Employee emp) throws Exception {
		int rc = -1;
		String query = "INSERT INTO Person(name, adress, zipCode, phoneNumber, email, jobType) VALUES('"
				+ emp.getName()
				+ "','"
				+ emp.getAdress()
				+ "',"
				+ emp.getZipCode()
				+ ","
				+ emp.getPhoneNumber()
				+ ",'"
				+ emp.getEmail() + "','" + emp.getJobType() + "')";

		System.out.println("insert : " + query);
		try {// insert new Employee
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (SQLException ex) {
			System.out.println("Employee ikke oprettet");
			throw new Exception("Employee is not inserted correct");
		}
		return (rc);
	}

	// this method updates the values on a customer object
	public int updateCustomer(Customer customer) {
		Customer cusObj = customer;
		int rc = -1;

		String query = "UPDATE person SET " + "customerType = '"
				+ cusObj.getCustomerType() + "', " + "companyName = '"
				+ cusObj.getCompanyName() + "', " + "cvrNr ='"
				+ cusObj.getCvrNr() + "', " + "phoneNumber ="
				+ cusObj.getPhoneNumber() + ", " + "name ='" + cusObj.getName()
				+ "', " + "adress ='" + cusObj.getAdress() + "', " + "email ='"
				+ cusObj.getEmail() + "', " + "zipCode =" + cusObj.getZipCode()
				+ " WHERE phoneNumber = " + cusObj.getPhoneNumber();

		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (Exception ex) {
			System.out.println("Update exception in Customer db: " + ex);
		}
		return (rc);
	}

	// this method updates an employee object
	public int updateEmployee(Employee employee) {
		Employee empObj = employee;
		int rc = -1;

		String query = "UPDATE person SET " + "jobType = '" + empObj.getJobType()
				+ "', " + "phoneNumber =" + empObj.getPhoneNumber() + ", "
				+ "name ='" + empObj.getName() + "', " + "adress = '"
				+ empObj.getAdress() + "', " + "email ='" + empObj.getEmail() + "', "
				+ "zipCode =" + empObj.getZipCode() 
				+ " WHERE phoneNumber = " + empObj.getPhoneNumber();

		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (Exception ex) {
			System.out.println("Update exception in Customer db: " + ex);
		}
		return (rc);
	}

	// this method deletes a customer object with the given phonenumber
	public int deletePerson(int phoneNumber) {
		int rc = -1;

		String query = "DELETE FROM person WHERE phoneNumber = '" + phoneNumber
				+ "'";
		System.out.println(query);
		try { // delete from person
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (Exception ex) {
			System.out.println("Delete exception in person db: " + ex);
		}
		return (rc);
	}

	// this method lists customers
	private ArrayList<Customer> customerMiscWhere(String wClause,
			boolean retriveAssociation) {
		ArrayList<Customer> customerList = new ArrayList<Customer>();
		ResultSet results;

		String query = buildCustomerQuery(wClause);
		System.out.print(query);

		try {// read the customer from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Customer cusObj = new Customer();
				cusObj = buildCustomer(results);
				customerList.add(cusObj);
			}// end while
			stmt.close();
			if (retriveAssociation) {
				IFDBSalesOrder dbSalesOrder = new DBSalesOrder();
				for (Customer cusObj : customerList) {
					cusObj.addSalesOrder(dbSalesOrder
							.findAllSalesOrdersOnCustomer(cusObj
									.getPhoneNumber()));
				}
			}// end if
		}// end try
		catch (Exception e) {
			System.out.println("Query exception - Select: " + e);
		}
		return customerList;
	}

	// this method lists employees
	private ArrayList<Employee> employeeMiscWhere(String wClause,
			boolean retriveAssociation) {
		ArrayList<Employee> employeeList = new ArrayList<Employee>();
		ResultSet results;

		String query = buildEmployeeQuery(wClause);
		System.out.println(query);

		try {// read the employee from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Employee empObj = new Employee();
				empObj = buildEmployee(results);
				employeeList.add(empObj);
			}// end while
			stmt.close();
			if (retriveAssociation) {
				IFDBSalesOrder dbSalesOrder = new DBSalesOrder();
				for (Employee empObj : employeeList) {
					empObj.addSalesOrder(dbSalesOrder
							.findAllSalesOrdersOnEmployee(empObj
									.getPhoneNumber()));
				}
			}// end if
		}// end try
		catch (Exception e) {
			System.out.println("Query exception - Select: " + e);
		}
		return employeeList;
	}

	// this method returns a single customer object
	private Customer singleWhereCustomer(String wClause,
			boolean retriveAssociation) {
		ResultSet results;
		Customer cusObj = new Customer();

		String query = buildCustomerQuery(wClause);
		System.out.println(query);
		try {// read the Customer from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				cusObj = buildCustomer(results);
				// association is to be build
				stmt.close();
				if (retriveAssociation) {
					IFDBSalesOrder dbSalesOrder = new DBSalesOrder();
					cusObj.addSalesOrder(dbSalesOrder
							.findAllSalesOrdersOnCustomer(cusObj
									.getPhoneNumber()));
				}
			} else {// no Customer was found
				cusObj = null;
			}
		}// end try
		catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return cusObj;
	}

	// this method returns a single employee object
	private Employee singleWhereEmployee(String wClause,
			boolean retriveAssociation) {
		ResultSet results;
		Employee empObj = new Employee();

		String query = buildEmployeeQuery(wClause);
		System.out.println(query);
		try {// read the Employee from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				empObj = buildEmployee(results);
				// association is to be build
				stmt.close();
				if (retriveAssociation) {
					IFDBSalesOrder dbSalesOrder = new DBSalesOrder();
					empObj.addSalesOrder(dbSalesOrder
							.findAllSalesOrdersOnEmployee(empObj
									.getPhoneNumber()));
				}
			} else {// no Employee was found
				empObj = null;
			}
		}// end try
		catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return empObj;
	}

	// method to build the customer-query
	private String buildCustomerQuery(String wClause) {
		String query = "SELECT * from person per, zipcodecity zipcity WHERE per.zipCode = zipcity.zipcode and customertype not like 'null'";
		if (wClause.length() > 0)
			query = query + "and " + wClause;
					
		
		return query;
	}

	// method to build the employee-query
	private String buildEmployeeQuery(String wClause) 
	{
		
		String query = "SELECT * from person per, zipcodecity zipcode WHERE per.zipCode = zipcode.zipcode and jobtype not like 'null'";
		if (wClause.length() > 0)
			query = query + "and " + wClause;
		return query;
	}

	// method to build an Customer object
	private Customer buildCustomer(ResultSet results) 
	{
		Customer cusObj = new Customer();
		try {// the columns from the table customer are used
			cusObj.setCustomerType(results.getString("customerType"));
			cusObj.setCompanyName(results.getString("companyName"));
			cusObj.setCvrNr(results.getInt("cvrNr"));
			cusObj.setPhoneNumber(results.getInt("phoneNumber"));
			cusObj.setName(results.getString("name"));
			cusObj.setAdress(results.getString("adress"));
			cusObj.setEmail(results.getString("email"));
			cusObj.setZipCode(results.getInt("zipCode"));
			cusObj.setCity(results.getString("city"));
		} catch (Exception e) {
			System.out.println("error in building the Customer object");
		}
		return cusObj;
	}

	// method to build an Employee object
	private Employee buildEmployee(ResultSet results) {
		Employee empObj = new Employee();
		try {// the columns from the table employee are used
			empObj.setName(results.getString("name"));
			empObj.setJobType(results.getString("jobType"));
			empObj.setPhoneNumber(results.getInt("phoneNumber"));
			empObj.setAdress(results.getString("adress"));
			empObj.setEmail(results.getString("email"));
			empObj.setZipCode(results.getInt("zipCode"));
			empObj.setCity(results.getString("city"));
		} catch (Exception e) {
			System.out.println("error in building the Employee object");
		}
		return empObj;
	}

	//Returns the city name when zipcode is entered
	public String getCityOnZipCode(int zipCode) {
		String wClause = "zipcode = '" + zipCode + "'";
		String city = singleWhereZipCode(wClause);
		return city;
	}

	//Searches the DB for a city name matching the entered zipcode
	public String singleWhereZipCode(String wClause)
		{
			ResultSet results = null;
			String city = "";
			
			String query = "Select * from ZipCodeCity where " + wClause;
			System.out.println(query);
			try{
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);
				
				results.next();
				
				city = results.getString("city");
				stmt.close();
			}
			
			
			catch (Exception e)
			{
				city = "City not found";
			}
			
			
			return city;
		}
}
